
from typing import Any
from nicegui import ui, events, app,run
import pandas as pd
import json
import logging
import shelve
from config import database_path,excel_sql_path
import shutil
import os
import webview
import activetable

# 流程数据的结构 excel_task
{'流程名称':'excel路径'}

class display_procedure():
    def __init__(self):
        with shelve.open(database_path) as db:
            excel_task=db['excel_task']    
        columns = [{'name': '流程名称','label': '流程名称', 'field': '流程名称', 'resizable': True,'align': 'left','sortable': True,},]
        
        rows=[{'流程名称': f'{task}'}  for task in excel_task  ]
        self.table=ui.table(columns=columns,rows=rows,row_key='流程名称',selection='multiple',on_select=lambda e: self.on_selects(e),pagination=5)
        self.table.style('height: 100%;  width: 100%;')
        self.table.props('title="流程任务名称表" auto-width ')
        self.table.on('rowDblclick', self.onRowDblclick) 
    
    def on_selects(self,e):
        
        self.on_select=e.selection
    def onRowDblclick(self,evt):
        datas=evt.args[1]
        crud_excel_db(args=datas,update_ui=self) 
    
    def update(self):
        
        with shelve.open(database_path) as db:
            rows=[{'流程名称': f'{task}'}  for task in db['excel_task']  ]
        self.table.rows=rows
        self.table.update()
            
class crud_excel_db():
    def __init__(self,args:dict={},update_ui=None) -> None:
        self.args=args  
        self.update_ui=update_ui
        self.filepath=None
        
        with ui.dialog(value=True).classes('w-full flex-nowrap') as self.dialog, ui.card().classes('w-full flex-nowrap'):
            self.procedure=ui.input('流程名称',value=args.get('流程名称','')).classes('w-96 flex-nowrap') 
            with ui.row():
                ui.button('添加流程文件',on_click=self.upload_window)
                ui.label('').bind_text(self,'filepath').classes('')
            
            with ui.row():
                    ui.button("关闭", on_click=lambda: self.dialog.close())
                    ui.button("增加",on_click=self.add)
                    ui.button("删除",on_click=self.delete)
                    ui.button("修改",on_click=self.update)
                    ui.button("下载流程文件",on_click=self.download_procedure)
    async def download_procedure(self):
        file = await app.native.main_window.create_file_dialog(
        webview.SAVE_DIALOG, save_filename=f"{self.procedure.value}",file_types=("excel文件 (*.xlsx;*.xls)",))
        with shelve.open(database_path) as db:
                filepath=db['excel_task'].get(self.procedure.value,None)
        
        shutil.copyfile(filepath,file)
                
                
    async def upload_window(self):
        files = await app.native.main_window.create_file_dialog(file_types=("excel文件 (*.xlsx;*.xls)",))
        if files is not None:
            self.filepath=files[0]

    def add(self):
        if self.filepath is not None:
            if self.filepath.endswith('.xlsx'):
                new_filepath=os.path.join(excel_sql_path,f'{self.procedure.value}.xlsx')
            else:
                new_filepath=os.path.join(excel_sql_path,f'{self.procedure.value}.xls')
            
            shutil.copy(self.filepath,new_filepath)
            with shelve.open(database_path,writeback=True) as db:
                db['excel_task'].update({self.procedure.value:new_filepath})
        
            self.update_ui.update()
            self.dialog.close()

    def delete(self):
        with shelve.open(database_path,writeback=True) as db:
            try :
                filename=db['excel_task'].pop(self.procedure.value)
            except:
                filename=None
        if  filename:
            try:
                os.remove(filename)
            except Exception as e:
                with ui.dialog(value=True) as dialog , ui.card() :
                    ui.label('错误原因:')
                    ui.code(str(e))
                    ui.button(text='确定',on_click=dialog.close)

         
        self.update_ui.update()
        self.dialog.close()
    def update(self):
        if self.filepath is not None:
            if self.filepath.endswith('.xlsx'):
                new_filepath=os.path.join(excel_sql_path,f'{self.procedure.value}.xlsx')
            else:
                new_filepath=os.path.join(excel_sql_path,f'{self.procedure.value}.xls')
            
            shutil.copy(self.filepath,new_filepath)
            with shelve.open(database_path,writeback=True) as db:
                db['excel_task'].update({self.procedure.value:new_filepath})
                
            self.update_ui.update()
            self.dialog.close()


class run_excel_sql():
    
    def __init__(self,grid,find_tableui):
        self.grid=grid
        self.find_tableui=find_tableui

    async def run(self,procedures_name_list,run_button:ui.button):
        ## 每次初始化
        with shelve.open(database_path) as db:
            db['tables_dict']={}
        
        
        for procedures in procedures_name_list:
            procedures_name=procedures['流程名称']
            with shelve.open(database_path) as db:
                # print(procedures_name,db['excel_task'])
                filepath=db['excel_task'].get(procedures_name,None)
            if filepath:
                # print(filepath)
                notification = ui.notification(message=f'开始任务{procedures_name}!!!',position='top',color='blue',spinner=True,timeout=60*60*2)
                run_button.disable()
                error= await run.cpu_bound(activetable.run,filepath)
                if error:
                    notification._props['options']['timeout']=5*1000
                    notification.message='任务失败!!!'
                    notification.color="red"
                    notification.spinner=False
                    notification.close_button=True
                    with ui.dialog(value=True) as self.dialog, ui.card().classes("w-full flex-nowrap"):
                        with ui.element('pre'):
                            ui.label(error)
                    
                else:
                    notification._props['options']['timeout']=5*1000
                    notification.spinner =False
                    notification.type='info'
                    notification.message='任务完成!!!'
                    notification.color="green"
                    notification.close_button=True
                
                # ui.notify(f'{procedures_name}运行成功')
                run_button.enable()
                find_table_ui.refresh(self.find_tableui)
                
    

def find_tables(tables_names,find_tableui:ui.grid):
    find_tableui.clear()
    with shelve.open(database_path) as db:
        tables_dict=db['tables_dict']
    # print(tables_names)
    for tables_name  in tables_names:
        table = tables_dict.get(tables_name,None)
        with find_tableui:
            if  table :
                with ui.expansion(tables_name, icon='menu').classes('w-full'):
                    df = pd.DataFrame(table)
                    columnDefs =[{'headerName': columns, 'field': columns,'resizable': True,'filter': 'agTextColumnFilter', 'floatingFilter': True} for columns in   df.columns.values]
                    aggrid = ui.aggrid({'columnDefs': columnDefs,'rowData': table})

async def download_result(tablenames):
    with shelve.open(database_path) as db:
        tables_dict=db['tables_dict']
    file = await app.native.main_window.create_file_dialog(
        webview.SAVE_DIALOG, save_filename="result",file_types=("excel文件 (*.xlsx;*.xls)",))
    if file is not None:
        with pd.ExcelWriter(file) as writer:
            for tablename in tablenames:
                df=pd.DataFrame(tables_dict[tablename])
                df.to_excel(writer, sheet_name=tablename, index=False)

@ui.refreshable
def find_table_ui(find_tableui):
    with shelve.open(database_path) as db:
            tables_dict:dict=db['tables_dict']
    if tables_dict:
        ui.button('查找表格',on_click=lambda:find_tables(select.value,find_tableui))
        ui.button('下载表格',on_click=lambda:download_result(select.value))
        select=ui.select(options=list(tables_dict.keys(),),multiple=True ,value=list(tables_dict.keys(),)[0]).classes('min-w-80 max-w-full')
    


def excel_sql():
    excel_=display_procedure()
    # 流程名称表
    procedure_gui=ui.grid(columns=1).classes('w-full')
    
    # 按钮行
    button_gui=ui.row()
    
    #  表格显示
    find_tableui=ui.grid(columns=1).classes('w-full')
    
    
    runexcel=run_excel_sql(procedure_gui,find_tableui)
    
    with button_gui:
        ui.button('新增任务',on_click=lambda:crud_excel_db(update_ui=excel_))
        run_button=ui.button('运行数据',on_click=lambda:runexcel.run(excel_.on_select,run_button))
        find_table_ui(find_tableui)



def page():
    with ui.expansion('帮助文档(点击展开)', icon='menu').classes('w-full'):
        with ui.row():
            ui.link('书写语法文档',new_tab=True,target='https://duanfu.blog.csdn.net/article/details/135343759').classes('text-2xl')
            ui.link('工具文档案例',new_tab=True,target='https://blog.csdn.net/qq_42077348/category_12544078.html').classes('text-2xl')
    
    excel_sql()